CREATE PROCEDURE Existencias
	 @IdProducto int
	,@Cantidad int
	,@IdCustomer varchar(100)
	,@Restante INT OUTPUT   
AS			

	declare @precio decimal 

		select  @precio = d.UnitPrice, @Restante = p.UnitsInStock - d.Quantity
		from [Order Details] as d 
		join Orders as o on d.OrderID = o.OrderID
		join Products as p on p.ProductID = d.ProductID

		declare @idorden int
		set @idorden = IDENT_CURRENT('Orders') + 1
	begin try
	begin transaction
		insert into Orders(OrderID,CustomerID,OrderDate) values (@idorden,@IdCustomer,CURRENT_TIMESTAMP)
		
		insert into [Order Details] values(@idorden,@IdProducto,@precio,@Cantidad,0)
	end try	
		/*update Products set UnitsInStock = @Restante
		where ProductID = @IdProducto*/
	begin catch

	 select @@ERROR
	 print error_message()
	 rollback transaction 
	
	end catch	
go  
